﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace FirmaTransportowa
{
    public class Worker : IModel<Worker>
    {
        [System.ComponentModel.DisplayName("ID pracownika")]
        public int id { get; set; }
        [System.ComponentModel.DisplayName("Imię")]
        public String name { get; set; }
        [System.ComponentModel.DisplayName("Nazwisko")]
        public String surname { get; set; }
        [System.ComponentModel.DisplayName("Data urodzenia")]
        public DateTime birthdate { get; set; }
        [System.ComponentModel.DisplayName("PESEL")]
        public long pesel { get; set; }
        [System.ComponentModel.DisplayName("ID zawodu")]
        public Occupation occupation { get; set; }

        public Boolean active { get; set; }

        public Worker(int id, string name, string surname, DateTime birthdate, long pesel, int occupation_id)
        {
            this.id = id;
            this.name = name;
            this.surname = surname;
            this.birthdate = birthdate;
            this.pesel = pesel;
            this.occupation = Occupation.getById(occupation_id);
            this.active = true;
        }

        public Worker()
        {

        }

        public string DisplayText
        {
            get
            {
                return this.name + " " + this.surname;
            }
        }

        public int ReturnValue
        {
            get
            {
                return this.id;
            }
        }

        public static void checkLicences(Worker worker, CheckedListBox listbox)
        {
            SqlConnection dbConnection2 = SqlConnectionFactory.getConnection();
            dbConnection2.Open();

            try
            {
                string sql = "SELECT * FROM Worker_Licence WHERE driver_ID='" + worker.id + "'";

                SqlCommand cmd = new SqlCommand(sql, dbConnection2);
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    int licenseid = reader.GetInt32(2);
                    switch (licenseid)
                    {
                        case 1:
                            listbox.SetItemCheckState(0, CheckState.Checked);
                            break;
                        case 2:
                            listbox.SetItemCheckState(1, CheckState.Checked);
                            break;
                        case 3:
                            listbox.SetItemCheckState(2, CheckState.Checked);
                            break;
                        case 4:
                            listbox.SetItemCheckState(3, CheckState.Checked);
                            break;
                        case 5:
                            listbox.SetItemCheckState(4, CheckState.Checked);
                            break;
                        case 6:
                            listbox.SetItemCheckState(5, CheckState.Checked);
                            break;
                        case 7:
                            listbox.SetItemCheckState(6, CheckState.Checked);
                            break;
                        case 8:
                            listbox.SetItemCheckState(7, CheckState.Checked);
                            break;
                    }
                }
                reader.NextResult();
            }
            finally
            {
                //reader.Close();
                dbConnection2.Close();
            }

        }


        public List<WorkerLicence> getLicences()
        {
            return WorkerLicence.getLicences(this.id);
        }

        public static int getIdByNameAndSurname(string name, string surname)
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT worker_ID FROM Worker WHERE name = '" + name + "' AND surname = '" + surname + "' ", conn);
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.Default);

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        return int.Parse(reader["worker_ID"].ToString());
                    }
                }
                reader.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }

            return -1;
        }

        public static int getIdByPesel(long pesel)
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT worker_ID FROM Worker WHERE pesel = '" + pesel + "'", conn);
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.Default);

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        return int.Parse(reader["worker_ID"].ToString());
                    }
                }
                reader.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }

            return -1;
        }

        public static bool checkIfRecordExists(string checkedText, string desiredTable)
        {
            bool result = false;

            SqlConnection dbConnection2 = SqlConnectionFactory.getConnection();

            dbConnection2.Open();

            try
            {
                string sql = "SELECT * FROM " + desiredTable + " WHERE pesel='" + checkedText + "' AND active=1";

                SqlCommand cmd = new SqlCommand(sql, dbConnection2);
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    result = true;
                    MessageBox.Show("Podany numer PESEL juz istnieje w bazie!");
                }
                reader.Close();
            }
            finally
            {
                dbConnection2.Close();
            }

            return result;
        }

        public int save()
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            int createdId = -1;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                bool newObject = false;

                if (Worker.getById(this.id) == null)
                {
                    cmd.CommandText = "INSERT INTO Worker(name,surname,birthdate,pesel,occupation_ID,active) VALUES(@name, @surname, @birthdate, @pesel, @occupation_ID, @active) SELECT CONVERT(int, SCOPE_IDENTITY())";
                    newObject = true;
                }
                else
                {
                    cmd.CommandText = "UPDATE Worker SET name=@name, surname=@surname, birthdate=@birthdate, pesel=@pesel, occupation_ID=@occupation_ID, active=@active WHERE worker_ID=@id";
                    cmd.Parameters.AddWithValue("@id", this.id);
                }
                
                cmd.Parameters.AddWithValue("@name", this.name);
                cmd.Parameters.AddWithValue("@surname", this.surname);
                cmd.Parameters.AddWithValue("@birthdate", this.birthdate);
                cmd.Parameters.AddWithValue("@pesel", this.pesel);
                cmd.Parameters.AddWithValue("@occupation_ID", this.occupation.id);
                cmd.Parameters.AddWithValue("@active", this.active);
                cmd.Connection = conn;

                if (newObject)
                {
                    createdId = (int)cmd.ExecuteScalar();
                }
                else
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {

            }
            finally
            {
                conn.Close();
            }

            return createdId;
        }

        public static List<Worker> list()
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            List<Worker> list = new List<Worker>();

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("Select * from Worker", conn);
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.Default);

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {

                        Worker worker = Worker.getById(int.Parse(reader["worker_ID"].ToString()));

                        list.Add(worker);

                    }
                }
                reader.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }

            return list;
        }

        public static BindingSource getWorkers()
        {
            SqlConnection connection = SqlConnectionFactory.getConnection();
            BindingSource bindingSource = new BindingSource();

            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand("SELECT worker_ID, name, surname, birthdate, pesel, occupation_ID FROM dbo.Worker WHERE active = 1", connection);
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);

                bindingSource.DataSource = dataTable;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                connection.Close();
            }

            return bindingSource;
        }

        public static Worker getById(int id)
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {
                if (id < 0)
                {
                    throw new ArgumentException();
                }

                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "SELECT * FROM Worker WHERE worker_ID=@id;";
                cmd.Parameters.AddWithValue("@id", id);
                cmd.Connection = conn;
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow);

                if (reader.HasRows)
                {
                    reader.Read();
                    Worker worker = new Worker();
                    worker.id = int.Parse(reader["worker_ID"].ToString());
                    worker.name = reader["name"].ToString();
                    worker.surname = reader["surname"].ToString();
                    worker.birthdate = DateTime.Parse(reader["birthdate"].ToString());
                    worker.pesel = long.Parse(reader["pesel"].ToString());
                    worker.occupation = Occupation.getById(int.Parse(reader["occupation_ID"].ToString()));
                    worker.active = Boolean.Parse(reader["active"].ToString());

                    conn.Close();
                    return worker;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + "\n" + e.StackTrace);
            }
            finally
            {
                conn.Close();

            }

            return null;

        }

        public static Worker getByPesel(long pesel)
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {

                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "SELECT * FROM Worker WHERE pesel=@pesel;";
                cmd.Parameters.AddWithValue("@pesel", pesel);
                cmd.Connection = conn;
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow);

                if (reader.HasRows)
                {
                    reader.Read();
                    Worker worker = new Worker();
                    worker.id = int.Parse(reader["worker_ID"].ToString());
                    worker.name = reader["name"].ToString();
                    worker.surname = reader["surname"].ToString();
                    worker.birthdate = DateTime.Parse(reader["birthdate"].ToString());
                    worker.pesel = long.Parse(reader["pesel"].ToString());
                    worker.occupation = Occupation.getById(int.Parse(reader["occupation_ID"].ToString()));
                    worker.active = Boolean.Parse(reader["active"].ToString());

                    conn.Close();
                    return worker;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + "\n" + e.StackTrace);
            }
            finally
            {
                conn.Close();

            }

            return null;

        }


        public static List<String> getDriversNamesAndSurnames()
        {
            List<String> driversNamesAndSurnames = new List<String>();

            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT name,surname FROM Worker WHERE occupation_ID=1;", conn);
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.Default);

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        String nameSurname = null;

                        nameSurname = reader["name"].ToString() + " " + reader["surname"].ToString();

                        driversNamesAndSurnames.Add(nameSurname);
                    }
                }
                reader.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }

            return driversNamesAndSurnames;
        }

        public void delete()
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {
                conn.Open();
                //conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "DELETE FROM Worker WHERE worker_ID=@id";
                cmd.Parameters.AddWithValue("@id", this.id);

                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + "\n" + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }
        }

        public static Predicate<Worker> ById(int id)
        {
            return delegate(Worker client)
            {
                return client.id == id;
            };
        }
    }
}
